<?php

class includesComponents extends sfComponents {

    public function executeLang(){

    }

    public function executeFixed(){
        /* buscamos un articulo que sea recomendable leer despues de este */
        $article_id = $this->id;
        $related_articles = $this->getRelatedArticles($article_id, 2);
        
        $article = null;
        if(isset($related_articles[0])){
            $article = ArticlePeer::retrieveByPK($related_articles[0]['article_id']);
        }

        if(is_object($article)){
            $this->article = $article;
        }else{
            $this->article = null;
        }
    }

    private function getRelatedArticles($article_id, $max = 2){ /* Los que han leído este artículo, también han visto */
        $articles = array();

        $con = Propel::getConnection();

        $query = "
            select %s as id, %s as account_id, %s as article_id, %s as created_at, count(%s) as usuarios_totales
            from %s
            where %s in(
                select %s
                from %s
                where %s in (
                    select %s
                    from %s inner join %s
                        on %s = %s
                    where %s='".$article_id."'
                        and %s = '1' and %s = '0'
                )
            ) and %s <> (".$article_id.")
            group by %s
            order by usuarios_totales desc
            limit %s
        ";

        $query = sprintf($query,
          ArticlePaymentsPeer::ID,
          ArticlePaymentsPeer::ACCOUNT_ID,
          ArticlePaymentsPeer::ARTICLE_ID,
          ArticlePaymentsPeer::CREATED_AT,
          ArticlePaymentsPeer::ACCOUNT_ID,
          ArticlePaymentsPeer::TABLE_NAME,
          ArticlePaymentsPeer::ARTICLE_ID,
          ArticlePaymentsPeer::ARTICLE_ID,
          ArticlePaymentsPeer::TABLE_NAME,
          ArticlePaymentsPeer::ACCOUNT_ID,
          ArticlePaymentsPeer::ACCOUNT_ID,
          ArticlePaymentsPeer::TABLE_NAME,
                ArticlePeer::TABLE_NAME,
                ArticlePeer::ID,
                ArticlePaymentsPeer::ARTICLE_ID,
                ArticlePeer::ENABLED,
                ArticlePeer::IS_DRAFT,
          ArticlePaymentsPeer::ARTICLE_ID,
          ArticlePaymentsPeer::ARTICLE_ID,
          ArticlePaymentsPeer::ARTICLE_ID,
          $max
        );

        $stmt = $con->prepare($query);
        $stmt->execute();

        while($rs = $stmt->fetch(PDO::FETCH_ASSOC))
        {
            $articles[] = array(
                "article_id"=>$rs['article_id']
            );
        }

        return $articles;
    }

    public function executeAutor(){
        $autor_id = $this->id;
        $autor = AccountPeer::retrieveByPK($autor_id);
        $this->autor = $autor;

        $c = new Criteria();
        $c->add(ArticlePeer::OWNER_ID, $autor_id);
        $c->add(ArticlePeer::ENABLED, 1);
        $c->add(ArticlePeer::IS_DRAFT, 0);
        $c->setLimit(5);
        $this->same_owner_arts = ArticlePeer::doselect($c);

        /*crear metodo que reciba ids y devuelva arreglo de usuarios practil */
        $ids = array();
        $ids[] = $autor->getPractilAccountId();

        $myLib = new myLib();
        $array_users = $myLib->getUsersFromPractil($ids);
        
        $this->array_users = $array_users;
    }

    public function executeAutores(){
        $autores_principales = array();
        $autores_principales = $this->getPrincipalAuthors(10);/* ultimos 10 autores principales */
        $this->autores_principales = $autores_principales;

        $ids = array();
        foreach($autores_principales as $ap){
            $account = AccountPeer::retrieveByPK($ap['user_id']);
            $ids[] = $account->getPractilAccountId();
        }

        $myLib = new myLib();
        $array_users = $myLib->getUsersFromPractil($ids);
        
        $this->array_users = $array_users;
    }

    public function executeCountry(){
        $c = new Criteria();
        $c->add(CountryPeer::IS_SHOWN, 1);
        $c->addAscendingOrderByColumn(CountryPeer::NAME);
        $this->countries = CountryPeer::doSelect($c);
    }

    public function executeFancyBoxes(){
        $c = new Criteria();
        $c->addDescendingOrderByColumn(AbusePeer::ID);
        $this->abuses = AbusePeer::doSelect($c);
    }

    public function executeGlobalJs(){

    }

    public function executeMiniSearch(){
        $c = new Criteria();
        $c->add(CountryPeer::IS_SHOWN, 1);
        $c->addAscendingOrderByColumn(CountryPeer::NAME);
        $this->countries = CountryPeer::doSelect($c);
    }

    public function executeDestacados(){
        $articulos_mas_puntuados = $this->getMostScoredArticles(10);/* 10 articulos  mas puntuados */
        $this->articulos_mas_puntuados = $articulos_mas_puntuados;

        $articulos_mas_vistos = $this->getMostViewedArticles(10);/* 10 articulos  mas vistos */
        $this->articulos_mas_vistos = $articulos_mas_vistos;

        $articulos_mas_recientes = $this->getMostRecentArticles(10);/* 10 articulos  mas recientes */
        $this->articulos_mas_recientes = $articulos_mas_recientes;

        $autores_mas_leidos = $this->getMostViewedAuthors(12);/* 10 autores + leidos */
        $this->autores_mas_leidos = $autores_mas_leidos;

        /* crear metodo que reciba ids y devuelva arreglo de usuarios practil */
        $ids = array();
        foreach($articulos_mas_puntuados as $amp){
            $article = ArticlePeer::retrieveByPK($amp['id']);
            $ids[] = $article->getAccount()->getPractilAccountId();
        }
        foreach($articulos_mas_vistos as $amv){
            $article = ArticlePeer::retrieveByPK($amp['id']);
            $ids[] = $article->getAccount()->getPractilAccountId();
        }
        foreach($articulos_mas_recientes as $amr){
            $article = ArticlePeer::retrieveByPK($amr->getId());
            $ids[] = $article->getAccount()->getPractilAccountId();
        }
        foreach($autores_mas_leidos as $amp){
            $account = AccountPeer::retrieveByPK($amp['owner_id']);
            $ids[] = $account->getPractilAccountId();
        }

        $lib = new myPractil();
        $url = $lib->url_practil_get_users($ids);

        $myLib = new myLib();
        $array_users = array();
        $array_users = $myLib->getUsersFromPractil($ids);
        
        $this->array_users = $array_users;
    }

    public function executeSubmenu(){
        $c = new Criteria();
        $c->add(CountryPeer::ID, $this->getUser()->getAttribute('scountry')->getId());
        $c->add(DetCountryCategoryPeer::IS_SHOWN, 1);
        $c->addJoin(DetCountryCategoryPeer::CATEGORY_ID, CategoryPeer::ID);
        $c->addJoin(DetCountryCategoryPeer::COUNTRY_ID, CountryPeer::ID);
        $c->addDescendingOrderByColumn(CategoryPeer::ID);
        $this->categories = CategoryPeer::doSelectWithI18n($c);
    }

    public function executeDashboardInterests(){
        $this->array_suattr = $this->getUser()->getAttribute('suattr');
    }

    public function executeSubscriptions(){
        $id = $this->id;
        $this->author = AccountPeer::retrieveByPK($id);
        $this->array_suattr = $this->getUser()->getAttribute('suattr');

    }

    public function executeBar(){
        $simpleSAMLAuth = new SimpleSAML_Auth_Simple('default-sp');
        $this->simpleSAMLAuth = $simpleSAMLAuth;
    }

    private function getPrincipalAuthors($max = 5){ /* con mas suscritos */
        $authors = array();

        $con = Propel::getConnection();
        $query = '
          SELECT %s as id, %s as user_id, COUNT(%s) as subscribers
          FROM %s INNER JOIN %s
          ON %s = %s
          GROUP BY %s
          ORDER BY %s, subscribers DESC
          LIMIT %s
        ';

        $query = sprintf($query,
          AccountSubscriptionsPeer::ID,
          AccountSubscriptionsPeer::USER_ID,
          AccountSubscriptionsPeer::SUBSCRIBER_ID,
          AccountSubscriptionsPeer::TABLE_NAME,
          AccountPeer::TABLE_NAME,
          AccountSubscriptionsPeer::USER_ID,
          AccountPeer::ID,
          AccountSubscriptionsPeer::USER_ID,
          AccountPeer::RANK,
          $max
        );

        $stmt = $con->prepare($query);
        $stmt->execute();

        //while ($rs->next())
        while($rs = $stmt->fetch(PDO::FETCH_ASSOC))
        {
          $authors[] = array(
              "id"=>$rs['id'],
              "user_id"=>$rs['user_id'],
              "subscribers"=>$rs['subscribers']);
        }

        return $authors;
    }

    private function getMostViewedAuthors($max = 5){ /* autores de los articulos mas leidos */
        $authors = array();

        $con = Propel::getConnection();
        $query = '
          SELECT DISTINCT(tb.owner_id) FROM
            (SELECT %s AS id, COUNT(%s) AS views, %s AS owner_id
            FROM %s
            LEFT JOIN %s
            ON %s = %s
            WHERE %s = %s
                AND %s=1 AND %s=0
            GROUP BY %s
            ORDER BY views, %s) AS tb
          ORDER BY owner_id DESC LIMIT %s
        ';

        $query = sprintf($query,
            ArticlePeer::ID,
            ArticleViewsPeer::IP,
            ArticlePeer::OWNER_ID,
            ArticlePeer::TABLE_NAME,
            ArticleViewsPeer::TABLE_NAME,
            ArticleViewsPeer::ARTICLE_ID,
            ArticlePeer::ID,
            ArticlePeer::COUNTRY_ID,
            $this->getUser()->getAttribute('scountry')->getId(),
                ArticlePeer::ENABLED,
                ArticlePeer::IS_DRAFT,
                ArticleViewsPeer::ARTICLE_ID,
                ArticleViewsPeer::CREATED_AT,
            $max
        );

        $stmt = $con->prepare($query);
        $stmt->execute();

        while($rs = $stmt->fetch(PDO::FETCH_ASSOC))
        {
            $authors[] = array(
              "owner_id"=>$rs['owner_id']
            );
        }

        return $authors;
    }

    private function getMostScoredArticles($max = 5){
        $articles = array();

        $con = Propel::getConnection();
        $query = '
          SELECT %s as id, COUNT(%s) as scores
          FROM %s LEFT JOIN %s
          ON %s = %s
          WHERE %s = %s
          AND %s = 1 AND %s = 0
          GROUP BY %s
          ORDER BY scores, %s DESC
          LIMIT %s
        ';

        $query = sprintf($query,
          ArticlePeer::ID,
          ArticleScoresPeer::SCORE,
          ArticlePeer::TABLE_NAME,
          ArticleScoresPeer::TABLE_NAME,
          ArticleScoresPeer::ARTICLE_ID,
          ArticlePeer::ID,
          ArticlePeer::COUNTRY_ID,
          $this->getUser()->getAttribute('scountry')->getId(),
          ArticlePeer::ENABLED,
          ArticlePeer::IS_DRAFT,
          ArticleScoresPeer::ARTICLE_ID,
          ArticlePeer::CREATED_AT,
          $max
        );

        $stmt = $con->prepare($query);
        $stmt->execute();

        //while ($rs->next())
        while($rs = $stmt->fetch(PDO::FETCH_ASSOC))
        {
          $articles[] = array(
              "id"=>$rs['id'],
              "scores"=>$rs['scores']);
        }

        return $articles;
    }

    private function getMostViewedArticles($max = 5){
        $articles = array();

        $con = Propel::getConnection();
        $query = '
          SELECT %s as id, COUNT(%s) as views
          FROM %s LEFT JOIN %s
          ON %s = %s
          WHERE %s = %s
          AND %s = 1 AND %s = 0
          GROUP BY %s
          ORDER BY views, %s DESC
          LIMIT %s
        ';

        $query = sprintf($query,
          ArticlePeer::ID,
          ArticleViewsPeer::IP,
          ArticlePeer::TABLE_NAME,
          ArticleViewsPeer::TABLE_NAME,
          ArticleViewsPeer::ARTICLE_ID,
          ArticlePeer::ID,
          ArticlePeer::COUNTRY_ID,
          $this->getUser()->getAttribute('scountry')->getId(),
          ArticlePeer::ENABLED,
          ArticlePeer::IS_DRAFT,
          ArticleViewsPeer::ARTICLE_ID,
          ArticleViewsPeer::CREATED_AT,
          $max
        );

        $stmt = $con->prepare($query);
        $stmt->execute();

        while($rs = $stmt->fetch(PDO::FETCH_ASSOC))
        {
          $articles[] = array(
              "id"=>$rs['id'],
              "views"=>$rs['views']
              );
        }

        return $articles;
    }

    private function getMostRecentArticles($max = 5){
        $articles = array();

        $c = new Criteria();
        $c->add(ArticlePeer::COUNTRY_ID, $this->getUser()->getAttribute('scountry')->getId());
        $c->add(ArticlePeer::ENABLED, 1);
        $c->add(ArticlePeer::IS_DRAFT, 0);
        $c->addDescendingOrderByColumn(ArticlePeer::CREATED_AT);
        $c->setLimit(10);
        $articles = ArticlePeer::doSelect($c);

        return $articles;
    }

}